package dao;


import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import utils.Constantes;
import utils.Utils;

import model.Atendimento;
import model.Habilitacao;
import model.Servico;

public class ServicoDao extends GenericDao{

	
	private int numerodecampos = 5;
		
	
		
		public ServicoDao(){
			super();
		}

		public PreparedStatement adicionar(Servico servico) throws SQLException{
				
				String sql = "insert into servico(descricao,preco,tipopreco,volumoso,tamanho) values (?,?,?,?,?)";
				
				this.setQuery(sql);
				ArrayList<Integer> indexes=new ArrayList<Integer>();
				for (int i=0;i<numerodecampos;i++)
					indexes.add(i,i+1);
				indexes.add(numerodecampos,null);
				
				PreparedStatement stmt = this.loadModelToPreparedStatement(servico,indexes);
				
				stmt.execute();	
				this.setQuery(stmt.toString());

				stmt.close();
				
				con.close();
				return stmt;
				
			}	

		

		public PreparedStatement apagar(Servico servico) throws SQLException{
			String sql = "delete from servico where idservico = ?";
			
			this.setQuery(sql);
			ArrayList<Integer> indexes=new ArrayList<Integer>();
			for (int i=0;i<numerodecampos;i++)
				indexes.add(i,null);
			indexes.add(numerodecampos,1);
			PreparedStatement stmt = this.loadModelToPreparedStatement(servico, indexes);
			
			stmt.execute();
			this.setQuery(stmt.toString());

			stmt.close();
			
			con.close();
			return stmt;
			
			
			

		}
		
		


		public PreparedStatement alterar(Servico servico) throws SQLException{
			
			String sql = "update servico set descricao=?,preco=?,tipopreco=?,volumoso=?,tamanho=?" +
					" where idservico = ? and ativo = 'S'";
			
			this.setQuery(sql);
			ArrayList<Integer> indexes=new ArrayList<Integer>();
			for (int i=0;i<=numerodecampos;i++)
				indexes.add(i,i+1);
			
			
			PreparedStatement stmt =this.loadModelToPreparedStatement(servico,indexes); 
				
			
			
			
			stmt.execute();
			
			this.setQuery(stmt.toString());

			stmt.close();
			
			con.close();
			return stmt;
		}
		
		

		
		public ArrayList<Servico> getServicos(Servico servicoBusca, int limite, int inicio) throws SQLException{
			String sql = "select * from servico where 1=1 and ativo = 'S'";	
			this.setQuery(sql);
			ArrayList<Integer> restrictions=this.prepareRestrictionsQuery(servicoBusca);
			this.addOrder("asc","descricao");
			this.addLimit(limite,inicio);
			PreparedStatement stmt =this.addRestrictions(servicoBusca, restrictions);
					
			ResultSet rs = stmt.executeQuery();
			Servico servico = null;
			ArrayList<Servico> servicos=new ArrayList<Servico>();
			
			while(rs.next()){
				servico = this.loadResultSetToModel(rs);
				servicos.add(servico);
				
			} 
			this.setQuery(stmt.toString());

			stmt.close();
			rs.close();
			con.close();
			
			return servicos;
		}
		
		public Integer countGetServicos(Servico servicoBusca) throws SQLException{
			String sql = "select count(*) as count from servico where 1=1 and ativo = 'S'";	
			this.setQuery(sql);
			ArrayList<Integer> restrictions=this.prepareRestrictionsQuery(servicoBusca);
			PreparedStatement stmt =this.addRestrictions(servicoBusca, restrictions);
					
			ResultSet rs = stmt.executeQuery();
			Integer count = new Integer(0);
			
			while(rs.next()){
				if (!Utils.isBlank(rs.getInt(Constantes.COUNT)))
					count=rs.getInt(Constantes.COUNT);
			} 
			this.setQuery(stmt.toString());

			stmt.close();
			rs.close();
			con.close();
			
			return count;
			
		}
		
		public ArrayList<Servico> getServicos(Atendimento atendimento) throws SQLException{
			String sql = "select * from servico ser where ativo = 'S' and idservico in " +
					"(select idservico from habilitacao where ativo = 'S' and idhabilitacao in " +
					"(select idhabilitacao from item where ativo = 'S' and idatendimento = ?));";	
			
			
			
			
			PreparedStatement stmt =con.prepareStatement(sql);
			stmt.setInt(1, atendimento.getId());
					
			ResultSet rs = stmt.executeQuery();
			Servico servico = null;
			ArrayList<Servico> servicos=new ArrayList<Servico>();
			
			while(rs.next()){
				servico = this.loadResultSetToModel(rs);
				servicos.add(servico);
				
			} 
			this.setQuery(stmt.toString());

			stmt.close();
			rs.close();
			con.close();
			
			return servicos;
		}
		
		
		private PreparedStatement addRestrictions(Servico servico,
				ArrayList<Integer> indexes) throws SQLException {
			PreparedStatement stmt=con.prepareStatement(this.getQuery());
			
			if (!Utils.isBlank(indexes.get(0)))
				stmt.setString(indexes.get(0), "%"+servico.getDescricao()+"%");
			
			if (!Utils.isBlank(indexes.get(1)))
				stmt.setFloat(indexes.get(1), servico.getPreco());
			
			if (!Utils.isBlank(indexes.get(2)))
				stmt.setString(indexes.get(2), servico.getTipopreco());
			
			if (!Utils.isBlank(indexes.get(3)))
				stmt.setBoolean(indexes.get(3), Utils.toBoolean(servico.getVolumoso()));

			if (!Utils.isBlank(indexes.get(4)))
				stmt.setString(indexes.get(4), servico.getTamanho());
			
			if (!Utils.isBlank(indexes.get(5)))
				stmt.setInt(indexes.get(5), servico.getId());
			
			return stmt;
		}

		private PreparedStatement loadModelToPreparedStatement(Servico servico,
				ArrayList<Integer> indexes) throws SQLException {
			PreparedStatement stmt=con.prepareStatement(this.getQuery());
			
			if (!Utils.isBlank(indexes.get(0)))
				stmt.setString(indexes.get(0), servico.getDescricao());
			
			if (!Utils.isBlank(indexes.get(1)))
				stmt.setFloat(indexes.get(1), servico.getPreco());
			
			if (!Utils.isBlank(indexes.get(2)))
				stmt.setString(indexes.get(2), servico.getTipopreco());
			
			if (!Utils.isBlank(indexes.get(3)))
				stmt.setBoolean(indexes.get(3), Utils.toBoolean(servico.getVolumoso()));

			if (!Utils.isBlank(indexes.get(4)))
				stmt.setString(indexes.get(4), servico.getTamanho());
			
			if (!Utils.isBlank(indexes.get(5)))
				stmt.setInt(indexes.get(5), servico.getId());
			
			return stmt;
		}
		

		private ArrayList<Integer> prepareRestrictionsQuery(Servico servico) {
			ArrayList<Integer> restrictions=new ArrayList<Integer>();	
			int index=0;
			String sql=this.getQuery();
			
			restrictions.add(0, null);
			if (!Utils.isBlank(servico.getDescricao())){
				sql+=" and descricao like ?";
				index++;
				restrictions.add(0, index);
			}
			
			restrictions.add(1, null);
			if (!Utils.isBlank(servico.getPreco())){
				sql+=" and preco = ?";
				index++;
				restrictions.add(1, index);
			}		
			
			restrictions.add(2, null);
			if (!Utils.isBlank(servico.getTipopreco())){
				sql+=" and tipopreco = ?";
				index++;
				restrictions.add(2, index);
			}			
			
			restrictions.add(3, null);
			if (!Utils.isBlank(servico.getVolumoso())){
				sql+=" and volumoso = ?";
				index++;
				restrictions.add(3, index);
			}	
			
			restrictions.add(4, null);
			if (!Utils.isBlank(servico.getTamanho())){
				sql+=" and tamanho = ?";
				index++;
				restrictions.add(4, index);
			}		
			
			restrictions.add(5, null);
			if (!Utils.isBlank(servico.getId())){
				sql+=" and idservico = ?";
				index++;
				restrictions.add(5, index);
			}
				
			this.setQuery(sql);
			return restrictions;
		}

		private Servico loadResultSetToModel(ResultSet rs) throws SQLException {
			Servico servico = new Servico();
			
			if (!Utils.isBlank(rs.getInt("idservico")))
				servico.setId(rs.getInt("idservico"));
			
			if (!Utils.isBlank(rs.getBoolean(Constantes.VOLUMOSO)))
				servico.setVolumoso(Utils.booleanToSN(rs.getBoolean(Constantes.VOLUMOSO)));
			
			if (!Utils.isBlank(rs.getString(Constantes.DESCRICAO)))
				servico.setDescricao(rs.getString(Constantes.DESCRICAO));
			
			if (!Utils.isBlank(rs.getString(Constantes.TIPOPRECO))){
				String tipopreco = rs.getString(Constantes.TIPOPRECO);
				if (tipopreco.equals("f"))
				servico.setTipopreco("Fixo");
				
				if (tipopreco.equals("a"))
				servico.setTipopreco("A partir de");
					
			}
			
			if (!Utils.isBlank(rs.getString(Constantes.TAMANHO))){
				String tamanho = rs.getString(Constantes.TAMANHO);
				if (tamanho.equals("c"))
					servico.setTamanho("Curto");
				
				if (tamanho.equals("m"))
					servico.setTamanho("Médio");
				
				if (tamanho.equals("l"))
					servico.setTamanho("Longo");
				
				if (tamanho.equals("e"))
					servico.setTamanho("Extra Longo");
				
				
			}
			
			if (!Utils.isBlank(rs.getFloat(Constantes.PRECO)))
				servico.setPreco(rs.getFloat(Constantes.PRECO));
			
			
			return servico;
		}

	

		

		public PreparedStatement desativar(Servico servico) throws SQLException {
			Habilitacao habilitacao = new Habilitacao();
			habilitacao.setServico(servico);
			ArrayList<Habilitacao> habilitacaos = new HabilitacaoDao().getHabilitacaos(habilitacao, 0, 0);
			for(int i=0;i<habilitacaos.size();i++){					
				new HabilitacaoDao().desativar(habilitacaos.get(i));
			}
			
			String sql = "update servico set ativo = 'N' where idservico = ?";
			this.setQuery(sql);
			ArrayList<Integer> indexes=new ArrayList<Integer>();
			for (int i=0;i<numerodecampos;i++)
				indexes.add(i,null);
			indexes.add(numerodecampos,1);
			PreparedStatement stmt = this.loadModelToPreparedStatement(servico, indexes);
			
			stmt.execute();
			
			this.setQuery(stmt.toString());

			stmt.close();
			
			con.close();
			return stmt;
		}


}
